/*
 * SynchUtils.java
 *
 * Created on February 5, 2009, 5:30 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package com.vacuela.phonecredits.util;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.ResourceBundle;
import net.sf.jxls.exception.ParsePropertyException;
import net.sf.jxls.transformer.XLSTransformer;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 *
 * @author nestor.carrasco
 */
public class SynchUtils {
       protected final Log log = LogFactory.getLog(getClass());
    private static ResourceBundle rbJdbc = null;
    private static ResourceBundle rbParams = null;
    private  Connection con = null;
    private PreparedStatement pStamtUpdate = null;
    private PreparedStatement pStamtEmailsQry = null;
    private  final static String EMAILS_LIST ="EMAILS_LIST";
    private BasicDataSource  dataSource = null;
    
    /** Creates a new instance of PhoneSynchronizer */
    
    public void openConnection() throws SQLException{
        try{
//            con = DriverManager.getConnection(rbJdbc.getString("urlConn").trim(),
//                    rbJdbc.getString("dbUser").trim(),
//                    rbJdbc.getString("dbPass").trim());
            con = dataSource.getConnection();
            pStamtUpdate = con.prepareStatement(rbJdbc.getString("updateSaldo").trim());
            pStamtEmailsQry =con.prepareStatement(rbParams.getString("getMailListQry").trim());
        }catch(SQLException sqle){
          log.info("Exception in openConection Method", sqle);
            throw sqle;
        }
    }
    
    public void closeConnection(){
        try{
            pStamtUpdate.close();
            con.close();
        }catch(SQLException sqle){
            //Logger
             log.info("Exception in closeConnection Method", sqle);
        }
    }
    
    public  void updateSaldoPhone(String date, String time, float saldo, String phone) throws SQLException {
        String[] dateSplit = date.split("/");
        try {
            pStamtUpdate.setFloat(1, saldo);
            pStamtUpdate.setString(2, dateSplit[2]+"-"+dateSplit[1]+"-"+dateSplit[0]);
            pStamtUpdate.setString(3, time);
            pStamtUpdate.setString(4, phone);
            pStamtUpdate.executeUpdate();
        } catch (SQLException ex) {
            //       writeLog(ex.getMessage().replace("'", "."));
            log.info("Exception updateSaldoPhone => " + ex);
            ex.printStackTrace();
        }
    }
    
    public Map<String, String> loadMap(String query) throws SQLException, SQLException {
        
        Map<String, String> mapValues = new HashMap<String, String>();
        ResultSet rs = null;
        Statement stmt = null;
        try {
            stmt = con.createStatement();
            rs = stmt.executeQuery(rbJdbc.getString(query));
            while (rs.next()) {
                String numero = rs.getString(1);
                String nip = rs.getString(2);
                mapValues.put(numero, nip);
            }
            
        } catch (SQLException ex) {
            //writeLog(ex.getMessage().replace("'", "."));
            log.info("Exception loadMap=> " + ex);
        }finally{
            if (rs != null){
                rs.close();
            }
            if ( stmt != null){
                stmt.close();
            }
            //System.out.print("Phones Loaded =>  " + mapValues.size());
            log.info("Phones Loaded =>  " + mapValues.size());
        }
        return mapValues;
    }
    
    public final String getConfigParam(String keyName){
        String value = rbParams.getString(keyName);
        if (value == null){
            value="";
        }else{
            value = value.trim();
        }
        return value;
    }
    
    public final Integer getIntConfigParam(String keyName){
        String value = getConfigParam(keyName);
        if ("".equals(value)){
            throw new RuntimeException("Config Param: " +  keyName + " is Null");
        }
        return Integer.parseInt(value);
    }
    static{
        ClassLoader loader = Thread.currentThread().getContextClassLoader();
        rbJdbc = ResourceBundle.getBundle("Jdbc", Locale.getDefault(), loader);
        rbParams =ResourceBundle.getBundle("PhoneConfig", Locale.getDefault(), loader);
        
//        try {
//            Class.forName(rbJdbc.getString("driver").trim());
//
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
    }
    
    public  String getEmailsList(){
        String mailList="";
        try{
            pStamtEmailsQry.setString(1,EMAILS_LIST);
            ResultSet rs = pStamtEmailsQry.executeQuery();
            if ( rs.next()){
                mailList = rs.getString(1);
            }else{
                mailList =   getConfigParam("toMail");
            }
        }catch(Exception e ){
              log.info("Exception getEmailsList=> " + e);
            mailList =   getConfigParam("toMail");
        }
        return mailList;
    }
    
    public void createFile() throws SQLException, ParsePropertyException, IOException{
        log.info("Creating the Excel Report");
        Statement stmt = con.createStatement();        List<Phone> lista = new ArrayList<Phone>();


        // let's get number of records to be able to implement size() method in ResultSetCollection class
        String countQuery = "SELECT COUNT(*) FROM PHONEPROFILE";
        ResultSet rs = stmt.executeQuery(countQuery);
        int count = 0;
        if (rs.next()) {
            count = rs.getInt(1);
        }
        log.info("Total Records =>>" +count);

        String query = rbParams.getString("queryReport");
        rs = stmt.executeQuery(query);
        while(rs.next()){
            Phone tel = new Phone();
            tel.setLocation(rs.getString("location"));
            tel.setNip(rs.getString("nip"));
            tel.setNumber(rs.getString("number"));
            tel.setSaldo(rs.getString("saldo"));
            tel.setSimNumber(rs.getString("simNumber"));
            tel.setTelcelDate(rs.getString("telcelDate"));
            tel.setTelcelTime(rs.getString("telcelTime"));
            lista.add(tel);
        }

        Map beans = new HashMap();
        beans.put("phone",lista);
        log.info("Before transfrom the Excel");
        XLSTransformer transformer = new XLSTransformer();
        transformer.transformXLS(rbParams.getString("templateFileName"), beans, rbParams.getString("destFileName"));
        
    }
    
    public void initDataSource(){
        dataSource = new BasicDataSource ();
        dataSource.setDriverClassName(rbJdbc.getString("driver").trim());
        dataSource.setUrl(rbJdbc.getString("urlConn").trim());
        dataSource.setUsername( rbJdbc.getString("dbUser").trim());
        dataSource.setPassword( rbJdbc.getString("dbPass").trim());
        dataSource.setMaxActive(2);

    }

    public void detroyDataSource() throws SQLException{
        dataSource.close();
    }

    public static void main(String[] args){
        SynchUtils syn = new SynchUtils();
        
        try{
            syn.initDataSource();
            syn.openConnection();
            //syn.loadPhones();
            syn.createFile();
            syn.closeConnection();
            syn.detroyDataSource();
        }catch(Exception e ){
            System.out.print("Exception " + e);
        }
        
    }
}
